#! /bin/bash

HIVE_HOME=/usr/bin/hive

${HIVE_HOME} -S -e "
create database if not exists oe_dwb;
use oe_dwb;
drop table if exists oe_dwb.dwb_entry_detail;
create table if not exists oe_dwb.dwb_entry_detail(
    -- fact_customer_relationship
    id                    int comment '意向id',
    create_date_time      string comment '创建时间',
    update_date_time      string comment '最后更新时间',
    customer_id           int comment '所属客户id',
    origin_type           string comment '数据来源,线上线下，NETSERVICE or PRESIGNUP 表示线上,其他的值表示线下',
    creator               string comment '负责这个意向信息的销售id【也就是咨询老师员工ID】',
    origin_channel        string comment '来源渠道',
    payment_state         string comment '支付状态',
    payment_time          string comment '支付状态变动时间',
    itcast_clazz_id       string comment '所属ems班级id',
    itcast_clazz_time     string comment '报班时间',
    ems_student_id        int comment 'ems的学生id',
    total_fee             decimal(38) comment '报名费总金额',
    -- dim_employee
    emp_id                int comment '员工id',
    real_name             string comment '员工的真实姓名',
    tdepart_id            int comment '直属部门',
    -- dim_itcast_clazz
    class_id              int comment 'ems课程id(非自增)',
    itcast_school_id    string comment 'ems校区ID',
    itcast_school_name  string comment 'ems校区名称',
    itcast_subject_id   string comment 'ems学科ID',
    itcast_subject_name string comment 'ems学科名称',
    --dim_scrm_department
    depart_id               int comment '部门id',
    name             string comment '部门名称',
    --fact_customer_clue
    customer_relationship_id string comment '客户关系id',
    --fact_customer_appeal
    customer_relationship_first_id string comment '第一条客户关系id',
    appeal_status                  string comment '申诉状态，0:待稽核 1:无效 2：有效'
)comment '报名宽表'
partitioned by (dt string comment '以实际导入/操作日期固定写入每张表')
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');


SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
set hive.exec.orc.compression.strategy=COMPRESSION;


insert into table oe_dwb.dwb_entry_detail partition (dt)
select
      r.id,
      r.create_date_time,
      r.update_date_time,
      r.customer_id,
      case when r.origin_type = 'NETSERVICE' or r.origin_type = 'PRESIGNUP' then 'online'
           else 'offline' end as origin_type,
      r.creator,
      r.origin_channel,
      r.payment_state,
      r.payment_time,
      r.itcast_clazz_id,
      r.itcast_clazz_time,
      r.ems_student_id,
      r.total_fee,
      e.id as emp_id,
      e.real_name,
      e.tdepart_id,
      c.id as class_id,
      c.itcast_school_id,
      c.itcast_school_name,
      c.itcast_subject_id,
      c.itcast_subject_name,
      d.id as depart_id,
      d.name,
      cc.customer_relationship_id,
      a.customer_relationship_first_id,
      case when a.appeal_status != 1 or a.appeal_status is null then 'valid'
           else 'invalid' end as appeal_status,
      '2024-08-22' as dt
from oe_dwd.fact_customer_relationship r
left join oe_dwd.dim_itcast_clazz c on r.itcast_clazz_id = c.id
left join oe_dwd.dim_employee e on r.creator = e.id
left join oe_dwd.dim_scrm_department d on e.tdepart_id = d.id
left join oe_dwd.fact_customer_clue cc on r.id = cc.customer_relationship_id
left join oe_dwd.fact_customer_appeal a on cc.customer_relationship_id = a.customer_relationship_first_id;
"